--------------------------------------------------------------------------------
-- @name: unique vs nonunique
-- @author: dion cho
-- @note: test case for the difference between unique index and nonunique index
--------------------------------------------------------------------------------

set pages 10000
set lines 200
set timing off
set trimspool on

@capture_on

-- 1. internal structure
drop table t1 purge;

create table t1(c1 number, c2 number);

create unique index t1_uni on t1(c1);

create index t1_non on t1(c2);

insert into t1 select level, level from dual connect by level <= 100;
commit;


col header_file new_value file_no
col header_block new_value block_no
select header_file, header_block from dba_segments where segment_name = 'T1_UNI';

select * from table(tpack.block_dump(&file_no, &block_no+1));


col header_file new_value file_no
col header_block new_value block_no
select header_file, header_block from dba_segments where segment_name = 'T1_NON';

select * from table(tpack.block_dump(&file_no, &block_no+1));

-- size?
drop table t1 purge;

create table t1(c1 number, c2 number);

create unique index t1_uni on t1(c1);

create index t1_non on t1(c2);


insert into t1 select level, level from dual connect by level <= 500000;
commit;

analyze index t1_uni validate structure;

select name, blocks from index_stats;

analyze index t1_non validate structure;

select name, blocks from index_stats;


-- 2. transaction & size
drop table t1 purge;

create table t1(c1 number, c2 number);

create unique index t1_uni on t1(c1);

create index t1_non on t1(c2);


insert into t1 select level, level from dual connect by level <= 100000;
commit;

delete from t1;
insert into t1 select level, level from dual connect by level <= 100000;
commit;

analyze index t1_uni validate structure;

select name, blocks from index_stats;

analyze index t1_non validate structure;

select name, blocks from index_stats;


-- why so big difference?
drop table t1 purge;

create table t1(c1 number, c2 number);

create unique index t1_uni on t1(c1);

create index t1_non on t1(c2);

insert into t1 values(1, 1);
commit;

delete t1 where c1 = 1;
insert into t1 values(1, 1);
commit;

col header_file new_value file_no
col header_block new_value block_no
select header_file, header_block from dba_segments where segment_name = 'T1_UNI';

select * from table(tpack.block_dump(&file_no, &block_no+1, 0));


col header_file new_value file_no
col header_block new_value block_no
select header_file, header_block from dba_segments where segment_name = 'T1_NON';

select * from table(tpack.block_dump(&file_no, &block_no+1, 0));



-- 3. dynamic sampling
drop table t1 purge;

create table t1(c1 number, c2 number);

create unique index t1_uni on t1(c1);

create index t1_non on t1(c2);

insert into t1 select level, level from dual connect by level <= 1000;

-- dynamic sampling
explain plan for
select * from t1
where c1 = 1;

select * from table(dbms_xplan.display);

drop index t1_uni;

alter table t1 add constraint t1_uk unique (c1) using index (create index t1_uk on t1(c1));

explain plan for
select * from t1
where c1 = 1;

select * from table(dbms_xplan.display);



explain plan for
select * from t1
where c2 = 1;

select * from table(dbms_xplan.display);


exec dbms_stats.gather_table_stats(user, 't1');


-- 4. cardinality (multi column sanity)
drop table t1 purge;

create table t1(c1 number, c2 number, c3 number, c4 number);

create unique index t1_uni on t1(c1, c2);
create index t1_non on t1(c3, c4);

insert into t1 select level, level, level, level
from dual
connect by level <= 1000;

commit;

exec dbms_stats.gather_table_stats(user, 't1');

drop table t2 purge;

create table t2(c1 number, c2 number);

insert into t2 select level, level 
from dual
connect by level <= 30000;

commit;

exec dbms_stats.gather_table_stats(user, 't2');

explain plan for
select /*+ opt_param('_optimizer_join_sel_sanity_check','false') */
	* 
from t1, t2
where t1.c3 = t2.c1 and t1.c4 = t2.c2;

@plan

select 1000 * 30000 / 1000 / 30000 from dual;


explain plan for
select * 
from t1, t2
where t1.c3 = t2.c1 and t1.c4 = t2.c2;

@plan

select 1000 * 30000 / 30000 from dual;


explain plan for
select * 
from t1, t2
where t1.c1 = t2.c1 and t1.c2 = t2.c2;

@plan

select 1000 * 30000 / 1000 from dual;



-- 5. pk constraint
drop table t1 purge;

create table t1(c1 number, c2 number);

alter table t1 add constraint t1_pk primary key (c1);

select index_name, uniqueness from user_indexes where table_name = 'T1';

alter table t1 drop constraint t1_pk;

alter table t1 add constraint t1_pk primary key (c1) using index (create index t1_n1 on t1(c1));

select index_name, uniqueness from user_indexes where table_name = 'T1';

alter table t1 drop constraint t1_pk;

drop index t1_n1;

create index t1_n1 on t1(c1);

alter table t1 add constraint t1_pk primary key (c1) using index t1_n1;

select index_name, uniqueness from user_indexes where table_name = 'T1';

alter table t1 drop constraint t1_pk;

alter table t1 add constraint t1_pk primary key (c1) deferrable;

select index_name, uniqueness from user_indexes where table_name = 'T1';

insert into t1(c1, c2) values(1, 1);
insert into t1(c1, c2) values(1, 2);

rollback;

set constraint t1_pk deferred;

insert into t1(c1, c2) values(1, 1);
insert into t1(c1, c2) values(1, 2);

commit;


@capture_off
